package tests;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import model.CommonValues;
import model.SystemDataManager;
import model.database.DBException;
import model.database.StoreDB;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import controller.SystemController;

public class StoreDBTests {

	private static final String TEST_PRODUCT_NAME = "MyTestProduct";
	private static final String TEST_USER_NAME = "MyTestUser";
	private static final String TEST_EMPLOYEE_USER_NAME = "test-employee-user";
	private static final String TEST_CUSTOMER_USER_NAME = "test-customer-user";
	private static final String TEST_PASSWORD = "MyTestPassword";
	private static final String TEST_NAME = "MyTestName";
	private static final String TEST_ADDRESS = "MyTestAddress";
	private static final int TEST_NUMBER = 123456;
	private static final int TEST_PAYMENT_METHOD = 0;
	private static final int TEST_PERMISSION = 0;
	private static final double TEST_PRODUCT_PRICE = 1234.56;
	private static final int TEST_AMOUNT = 2;

	@Before
	public void setup() {
		
		System.out.println("in setup");
		// connection to database
		Connection connection = null;
		MysqlDataSource ds = new MysqlConnectionPoolDataSource();
		
    	// set server and database        	
    	ds.setServerName(StoreDB.SERVER_NAME);
    	ds.setDatabaseName(StoreDB.DB_NAME);

		try {
			
			connection = ds.getConnection(StoreDB.DB_USER, StoreDB.DB_PWD);
			Statement statement = connection.createStatement();

			// create user record
			// create query string
			String query = String.format(
					"INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
					StoreDB.PASSWORDS_TABLE, StoreDB.USER_NAME, StoreDB.PASSWORD, StoreDB.PERMISSION,
					TEST_USER_NAME, TEST_PASSWORD, TEST_PERMISSION);

			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add product
				fail();
			}
			
			// create an employee record
			// create query string
			query = String.format(
					"INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
					StoreDB.EMPLOYEES_TABLE, StoreDB.NUMBER, StoreDB.NAME, StoreDB.USER_NAME,
					TEST_NUMBER, TEST_NAME, TEST_EMPLOYEE_USER_NAME);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add employee
				fail();
			}
			
			// set user-name for employee
			query = String.format(
					"INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
					StoreDB.PASSWORDS_TABLE, StoreDB.USER_NAME, StoreDB.PASSWORD, StoreDB.PERMISSION,
					TEST_EMPLOYEE_USER_NAME, TEST_PASSWORD, CommonValues.permissions.EMPLOYEE.ordinal());

			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add product
				fail();
			}
			
			// create a customer record
			// create query string
			query = String.format(
					"INSERT INTO %s (%s, %s, %s, %s, %s) VALUES ('%s', '%s', '%s', '%s', '%s')",
					StoreDB.CUSTOMERS_TABLE, StoreDB.NUMBER, StoreDB.NAME, StoreDB.USER_NAME, StoreDB.ADDRESS, StoreDB.PAYMENT_METHOD,
					TEST_NUMBER, TEST_NAME, TEST_CUSTOMER_USER_NAME, TEST_ADDRESS, TEST_PAYMENT_METHOD);

			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add customer
				fail();
			}
			
			// set user-name for customer
			query = String.format(
					"INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
					StoreDB.PASSWORDS_TABLE, StoreDB.USER_NAME, StoreDB.PASSWORD, StoreDB.PERMISSION,
					TEST_CUSTOMER_USER_NAME, TEST_PASSWORD, CommonValues.permissions.CUSTOMER.ordinal());
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add customer user-name
				fail();
			}
			
			// create a product record
			// create query string
			query = String.format(
					"INSERT INTO %s (%s, %s) VALUES ('%s', '%s')",
					StoreDB.PRODUCTS_TABLE, StoreDB.NAME, StoreDB.PRICE,
					TEST_PRODUCT_NAME, TEST_PRODUCT_PRICE);

			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add product
				fail();
			}

			// create order record
			// create query string
			query = String.format(
					"INSERT INTO %s (%s, %s, %s, %s, %s, %s) VALUES ('%s', CURDATE(), '%s', '%s', '%s', '%s')",
					StoreDB.ORDERS_TABLE, StoreDB.NUMBER, StoreDB.DATE, StoreDB.ORDERING_CUSTOMER_NUMBER, StoreDB.NAME, 
					StoreDB.AMOUNT, StoreDB.TOTAL_SUM,
					TEST_NUMBER, TEST_NUMBER, TEST_PRODUCT_NAME, TEST_AMOUNT, TEST_AMOUNT*TEST_PRODUCT_PRICE);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add product
				fail();
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			fail();
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

	@After
	public void teardown() {
		System.out.println("in teardown");
		// connection to database
		Connection connection = null;
		MysqlDataSource ds = new MysqlConnectionPoolDataSource();

		// set server and database
		ds.setServerName(StoreDB.SERVER_NAME);
		ds.setDatabaseName(StoreDB.DB_NAME);

		try {
			connection = ds.getConnection(StoreDB.DB_USER, StoreDB.DB_PWD);

			Statement statement = connection.createStatement();

			// delete test user record
			String query = String.format("DELETE FROM %s WHERE %s = '%s'", 
					StoreDB.PASSWORDS_TABLE, StoreDB.USER_NAME, TEST_USER_NAME);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete user record
				fail();
			}
			
			// delete test employee record
			query = String.format("DELETE FROM %s WHERE %s = %s", 
					StoreDB.EMPLOYEES_TABLE, StoreDB.NUMBER, TEST_NUMBER);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete employee
				fail();
			}
			
			// delete test employee's user record
			query = String.format("DELETE FROM %s WHERE %s = '%s'", 
					StoreDB.PASSWORDS_TABLE, StoreDB.USER_NAME, TEST_EMPLOYEE_USER_NAME);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete employee's user
				fail();
			}
			
			// delete test customer record
			query = String.format("DELETE FROM %s WHERE %s = %s", 
					StoreDB.CUSTOMERS_TABLE, StoreDB.NUMBER, TEST_NUMBER);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete customer
				fail();
			}
			
			// delete test customer's user record
			query = String.format("DELETE FROM %s WHERE %s = '%s'", 
					StoreDB.PASSWORDS_TABLE, StoreDB.USER_NAME, TEST_CUSTOMER_USER_NAME);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete customer's user
				fail();
			}
			
			// delete test product record
			query = String.format("DELETE FROM %s WHERE %s = '%s' ", 
					StoreDB.PRODUCTS_TABLE, StoreDB.NAME, TEST_PRODUCT_NAME);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete product
				fail();
			}
			
			// delete test order record
			query = String.format("DELETE FROM %s WHERE %s = %s", 
					StoreDB.ORDERS_TABLE, StoreDB.NUMBER, TEST_NUMBER);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete order
				fail();
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			fail();
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
		System.out.println("done teardown");
	}

	@Test
	public void addRecordTest() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add user record
			boolean couldAdd = controller.addUserRecord("test user",CommonValues.permissions.EMPLOYEE);
			
			assertEquals(false, couldAdd);

		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}

	}

	@Test
	public void addEmployeeTest() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add employee
			boolean couldAdd = controller.addEmployee(1, "dada", "dada123", CommonValues.permissions.EMPLOYEE);
			assertEquals(false, couldAdd);

		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}
	}
	
	@Test
	public void createCustomerAccountTest() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add customer
			boolean couldAdd = controller.createCustomerAccount(1, "jimmy",
					"1 Zepplin st, London", CommonValues.paymentMethods.CREDIT);
						
			assertEquals(false, couldAdd);

		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}

	}

	@Test
	public void addNewProductTest() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add product
			boolean couldAdd = controller.addNewProduct("guitar", 700);
			
			assertEquals(false, couldAdd);

		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}
	}

	@Test
	public void addNewOrderTest() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add employee
			boolean couldAdd = controller.addNewOrder(1, "guitar", 1, 1);
			
			assertEquals(false, couldAdd);

		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}
	}
	
	@Test
	public void updateNonExistingProductPriceTest() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add update product price- PRODUCT DOES NOT EXIST
			boolean couldUpdate = controller.updateProductPrice("blah blah", 700);
			
			assertEquals(false, couldUpdate);

		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}
	}

	@Test
	public void updateProductPriceTest() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add update product price- PRODUCT EXISTS
			boolean couldUpdate = controller.updateProductPrice(TEST_PRODUCT_NAME, 650);
			
			assertEquals(true, couldUpdate);

		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}
	}

	@Test
	public void updateNonExistingCustomerDetailsTest() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add update customer details- CUSTOMER DOES NOT EXIST
			boolean couldUpdate = controller.updateCustomerDetails(0, "blah",
					"1 blah st, blahville", CommonValues.paymentMethods.CREDIT);
			assertEquals(false, couldUpdate);

		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}
	}

	@Test
	public void updateCustomerDetailsTest() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add update product price- PRODUCT EXISTS
			boolean couldUpdate = controller.updateCustomerDetails(TEST_NUMBER, "jimmy",
					"10 Rockstar Blv, Rocktown",
					CommonValues.paymentMethods.CHECK);
			assertEquals(true, couldUpdate);

		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}
	}

	@Test
	public void getProductsTest() {
		
		try {
			
			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);
			
			// get all products			
			List<Map<String, String>> result  = controller.getProducts();
			
			List<Map<String, String>> expected = new LinkedList<Map<String,String>>();
			
			Map<String, String> firstProduct = new HashMap<String, String>();
			firstProduct.put(CommonValues.NAME, "guitar");
			firstProduct.put(CommonValues.PRICE, ""+700.0);
			expected.add(firstProduct);
			
			Map<String, String> secondProduct = new HashMap<String, String>();
			secondProduct.put(CommonValues.NAME, TEST_PRODUCT_NAME);
			secondProduct.put(CommonValues.PRICE, ""+TEST_PRODUCT_PRICE);
			expected.add(secondProduct);
			
			assertEquals(expected, result);
			
		} catch (DBException e) {
			
			e.printStackTrace();
			fail();
		}		
	}

	@Test
	public void getProductPriceTest() {
		
		try {
			
			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);
			
			// get all products 
			double result  = controller.getProductPrice(TEST_PRODUCT_NAME);
			
			double expected = TEST_PRODUCT_PRICE;
			
			assertEquals(expected, result, 0);
			
		} catch (DBException e) {
			
			e.printStackTrace();
			fail();
		}		
	}

	@Test
	public void getEmployeesTest() {
		
		try {
			
			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);
			
			// get all products			
			List<Map<String, String>> result  = controller.getEmployees();
			
			List<Map<String, String>> expected = new LinkedList<Map<String,String>>();
			
			Map<String, String> firstEmployee = new HashMap<String, String>();
			firstEmployee.put(CommonValues.NUMBER, ""+1);
			firstEmployee.put(CommonValues.NAME, "dada");
			firstEmployee.put(CommonValues.USER_NAME, "dada123");
			expected.add(firstEmployee);
			
			Map<String, String> secondEmployee = new HashMap<String, String>();
			secondEmployee.put(CommonValues.NUMBER, ""+TEST_NUMBER);
			secondEmployee.put(CommonValues.NAME, TEST_NAME);
			secondEmployee.put(CommonValues.USER_NAME, TEST_EMPLOYEE_USER_NAME);
			expected.add(secondEmployee);
			
			assertEquals(expected, result);
			
		} catch (DBException e) {
			
			e.printStackTrace();
			fail();
		}		
	}
	
	@Test
	public void getCustomersTest() {
		
		// create database
		StoreDB db;
		
		try {
			
			db = new StoreDB();
			
			// get all products			
			List<Map<String, String>> result  = db.getCustomers();
			
			List<Map<String, String>> expected = new LinkedList<Map<String,String>>();
			
			Map<String, String> firstCustomer = new HashMap<String, String>();
			firstCustomer.put(CommonValues.NUMBER, ""+1);
			firstCustomer.put(CommonValues.NAME, "jimmy");
			firstCustomer.put(CommonValues.USER_NAME, ""+1);
			firstCustomer.put(CommonValues.ADDRESS, "1 Zepplin st, London");
			firstCustomer.put(CommonValues.PAYMENT_METHOD, ""+1);
			expected.add(firstCustomer);
			
			Map<String, String> secondCustomer = new HashMap<String, String>();
			secondCustomer.put(CommonValues.NUMBER, ""+TEST_NUMBER);
			secondCustomer.put(CommonValues.NAME, TEST_NAME);
			secondCustomer.put(CommonValues.USER_NAME, TEST_CUSTOMER_USER_NAME);
			secondCustomer.put(CommonValues.ADDRESS, TEST_ADDRESS);
			secondCustomer.put(CommonValues.PAYMENT_METHOD, ""+TEST_PAYMENT_METHOD);
			expected.add(secondCustomer);
			
			assertEquals(expected, result);
			
		} catch (DBException e) {
			
			e.printStackTrace();
			fail();
		}		
	}

	@Test
	public void getOrdersByCustomerTest() {
		
		// create database
		StoreDB db;
		
		try {
			
			db = new StoreDB();
			
			// get all products			
			List<Map<String, String>> result  = db.getOrders(TEST_NUMBER);
			
			List<Map<String, String>> expected = new LinkedList<Map<String,String>>();
			
			Map<String, String> firstOrder = new HashMap<String, String>();
			firstOrder.put(CommonValues.NUMBER, ""+TEST_NUMBER);
			firstOrder.put(CommonValues.DATE, "2013-05-25");
			firstOrder.put(CommonValues.ORDERING_CUSTOMER_NUMBER, ""+TEST_NUMBER);
			firstOrder.put(CommonValues.NAME, TEST_PRODUCT_NAME);
			firstOrder.put(CommonValues.AMOUNT, ""+TEST_AMOUNT);
			firstOrder.put(CommonValues.TOTAL_SUM, ""+(TEST_AMOUNT*TEST_PRODUCT_PRICE));
			expected.add(firstOrder);
						
			assertEquals(expected, result);
			
		} catch (DBException e) {
			
			e.printStackTrace();
			fail();
		}		
	}

	@Test
	public void getCustomersByProductTest() {
		
		// create database
		StoreDB db;
		
		try {
			
			db = new StoreDB();
			
			// get all products			
			List<Map<String, String>> result  = db.getCustomers(TEST_PRODUCT_NAME);
			
			List<Map<String, String>> expected = new LinkedList<Map<String,String>>();
			
			Map<String, String> firstCustomer = new HashMap<String, String>();
			firstCustomer.put(CommonValues.NUMBER, ""+TEST_NUMBER);
			firstCustomer.put(CommonValues.NAME, TEST_NAME);
			firstCustomer.put(CommonValues.USER_NAME, TEST_CUSTOMER_USER_NAME);
			firstCustomer.put(CommonValues.ADDRESS, TEST_ADDRESS);
			firstCustomer.put(CommonValues.PAYMENT_METHOD, ""+TEST_PAYMENT_METHOD);
			expected.add(firstCustomer);
						
			assertEquals(expected, result);
			
		} catch (DBException e) {
			
			e.printStackTrace();
			fail();
		}		
	}

	@Test
	public void getCustomersByOrderSumTest() {
		
		// create database
		StoreDB db;
		
		try {
			
			db = new StoreDB();
			
			// get all products			
			List<Map<String, String>> result  = db.getCustomers(TEST_PRODUCT_PRICE-1);
			
			List<Map<String, String>> expected = new LinkedList<Map<String,String>>();
			
			Map<String, String> firstCustomer = new HashMap<String, String>();
			firstCustomer.put(CommonValues.NUMBER, ""+TEST_NUMBER);
			firstCustomer.put(CommonValues.NAME, TEST_NAME);
			firstCustomer.put(CommonValues.USER_NAME, TEST_CUSTOMER_USER_NAME);
			firstCustomer.put(CommonValues.ADDRESS, TEST_ADDRESS);
			firstCustomer.put(CommonValues.PAYMENT_METHOD, ""+TEST_PAYMENT_METHOD);
			expected.add(firstCustomer);
						
			assertEquals(expected, result);
			
		} catch (DBException e) {
			
			e.printStackTrace();
			fail();
		}		
	}

	@Test
	public void getSalesReportByDayTest() {
		
		// create database
		StoreDB db;
		
		try {
			
			db = new StoreDB();
			
			 
			double result  = db.getSalesReport("2013-05-25");			
			double expected = 2*TEST_PRODUCT_PRICE + 700;
			
			assertEquals(expected, result, 0);
			
		} catch (DBException e) {
			
			e.printStackTrace();
			fail();
		}		
	}
	
	@Test
	public void getSalesReportByMonthTest() {
		
		// create database
		StoreDB db;
		
		try {
			
			db = new StoreDB();
			
			 
			double result  = db.getSalesReport(6);			
			double expected = 0;
			
			assertEquals(expected, result, 0);
			
		} catch (DBException e) {
			
			e.printStackTrace();
			fail();
		}		
	}
}
